﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DALBase;
using System.Collections;
using OneMile.Entity.Model;
using System.Data;
using OneMile.Common;
using OneMile.Entity;

namespace OneMile.DAL
{
   public class DALUFavorites:BaseDAL
    {
       public DBAccess db = DBConnection.CreateDB();

       public bool IsFavoriteExit(string userId,string shopId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("SELECT COUNT(1) FROM Fct_UserCollection WHERE Disabled=0 AND UserId=@UserId AND ShopsId=@ShopsId");
               Hashtable hs = new Hashtable();
               hs.Add("UserId", userId);
               hs.Add("ShopsId", shopId);
               int num = Convert.ToInt32(db.ExecuteScalar(sb.ToString(), hs));
               if (num == 1)
               {
                   return true;
               }
               else
               {
                   return false;
               }
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               throw e;
           }
       }

       public bool DelFavorite(string userCollectionId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("update Fct_UserCollection ");
               sb.AppendLine("set Disabled=1 ");
               sb.AppendLine("where UserCollectionId = @UserCollectionId");
               Hashtable hs = new Hashtable();
               hs.Add("UserCollectionId", userCollectionId);
               int num = Convert.ToInt32(db.ExecuteNonQuery(sb.ToString(),hs));
               if (num == 1)
               {
                   return true;
               }
               else
               {
                   return false;
               }
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               throw;
           }
       }

       public bool AddNewFavorite(EUserCollection fav)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("INSERT INTO Fct_UserCollection ");
               sb.AppendLine("(UserCollectionId,UserId,ShopsId,Disabled,Rec_CreateTime,Rec_CreateBy,Rec_ModifyTime,Rec_ModifyBy)");
               sb.AppendLine("    VALUES");
               sb.AppendLine("         (");             
               sb.AppendLine("           @UserCollectionId,");
               sb.AppendLine("           @UserId,");
               sb.AppendLine("           @ShopsId,");
               sb.AppendLine("           @Disabled,");
               sb.AppendLine("           @Rec_CreateTime,");
               sb.AppendLine("           @Rec_CreateBy,");
               sb.AppendLine("           @Rec_ModifyTime,");
               sb.AppendLine("           @Rec_ModifyBy");
               sb.AppendLine("         )");
               Hashtable hs = new Hashtable();
               hs.Add("UserCollectionId", fav.UserCollectionId);
               hs.Add("UserId", fav.UserId);
               hs.Add("ShopsId", fav.ShopsId);
               hs.Add("Disabled", fav.Disabled);
               hs.Add("Rec_CreateTime", DateTime.Now);
               hs.Add("Rec_CreateBy", fav.Rec_CreateBy);
               hs.Add("Rec_ModifyTime", DateTime.Now);
               hs.Add("Rec_ModifyBy", fav.Rec_ModifyBy);

               int num = Convert.ToInt32(db.ExecuteNonQuery(sb.ToString(), hs));
               if (num == 1)
               {
                   return true;
               }
               else
               {
                   return false;
               }
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               throw e;
           }
       }

       public string GetSortedSQL(string sql, int SortField, int SortType)
       {
           String sql2 = null;
           switch (SortField)
           {
               case (int)Enums.EnumShopSortField.ShopsName:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsName.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumShopSortField.Consumption:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.Consumption.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               default:
                   sql2 = String.Format(sql, Enums.EnumShopSortField.ShopsName.ToString(), "DESC");
                   break;
           }
           return sql2;
       }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="userId"></param>
       /// <param name="pageNo"></param>
       /// <param name="pageSize"></param>
       /// <param name="shopsType">为空的时候表示不根据种类排序</param>
       /// <param name="SortField"></param>
       /// <param name="SortType"></param>
       /// <returns></returns>
       public IList<EShopsInfo> GetFavoritesListByUser(string userId, int pageNo, int pageSize, string shopsType, int SortField, int SortType)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine(" SELECT UserCollectionId,ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,OpeningTime,ClosingTime,Freight,DeliveryDistance,LowestPrice,Rec_CreateTime");
               sb.AppendLine("   FROM (");
               sb.AppendLine("         SELECT A.UserCollectionId,A.Rec_CreateTime,B.ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,");
               sb.AppendLine("                DeliveryDistance,LowestPrice,OpeningTime,ClosingTime,Freight,ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum");
               sb.AppendLine("           FROM Fct_UserCollection A");
               sb.AppendLine("     INNER JOIN Fct_Shops B ON A.ShopsId=B.ShopsId AND B.Disabled=0");
               sb.AppendLine("          WHERE A.Disabled=0  ");
               sb.AppendLine("            AND A.UserId=@UserId");
               if (!String.IsNullOrEmpty(shopsType))
               {
                  sb.AppendLine("        AND B.ShopsType LIKE @ShopType ");
               }
               sb.AppendLine("        ) AS D");
               sb.AppendLine("  WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize");

               Hashtable hs = new Hashtable();
               hs.Add("UserId", userId);
               hs.Add("PageNo", pageNo);
               hs.Add("PageSize", pageSize);
               if (!String.IsNullOrEmpty(shopsType))
               {
                   hs.Add("ShopType", String.Format("%{0}%", shopsType));
               }

               string sql = GetSortedSQL(sb.ToString(), SortField, SortType);
               DataTable dt = db.ExecuteQuery(sql, hs);
               IList<EShopsInfo> list = null;
               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo shop = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       shop = new EShopsInfo();
                       Guid userCollectionId = new Guid(dt.Rows[i]["UserCollectionId"].TString());
                       Guid shopId = new Guid(dt.Rows[i]["ShopsId"].TString());
                       shop.UserCollectionId = userCollectionId;
                       shop.ShopsId = shopId;
                       shop.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       shop.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       shop.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       shop.OpeningTime = dt.Rows[0]["OpeningTime"].TTime();
                       shop.ClosingTime = dt.Rows[0]["ClosingTime"].TTime();
                       shop.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       shop.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       shop.Freight = dt.Rows[0]["Freight"].TDecimal();
                       shop.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       shop.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       shop.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       shop.Rec_CreateTime = dt.Rows[i]["Rec_CreateTime"].TDateTime();
                       list.Add(shop);
                   }
               }
               return list;
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               return null;
           }
       }

       public IList<EShopsInfo> GetFavoritesListByUserLocation(string userId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("    SELECT A.UserCollectionId,A.Rec_CreateTime,B.ShopsId,ShopsName,ShopsType,ShopsPraise,ShopsPoor,Consumption,RecommendedDishes,OpeningTime,ClosingTime,Freight,DeliveryDistance,LowestPrice,Latitude,Longitude");
               sb.AppendLine("      FROM Fct_UserCollection A");
               sb.AppendLine("INNER JOIN Fct_Shops B ON A.ShopsId=B.ShopsId AND B.Disabled=0");
               sb.AppendLine("     WHERE A.Disabled=0 AND A.UserId=@UserId");
       
               Hashtable hs = new Hashtable();
               hs.Add("UserId", userId);
             
               DataTable dt = db.ExecuteQuery(sb.TString(), hs);
               IList<EShopsInfo> list = null;
               if (dt.Rows.Count > 0)
               {
                   list = new List<EShopsInfo>();
                   EShopsInfo shop = null;
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       shop = new EShopsInfo();
                       Guid userCollectionId = new Guid(dt.Rows[i]["UserCollectionId"].TString());
                       Guid shopId = new Guid(dt.Rows[i]["ShopsId"].TString());
                       shop.UserCollectionId = userCollectionId;
                       shop.ShopsId = shopId;
                       shop.ShopsName = dt.Rows[i]["ShopsName"].TString();
                       shop.ShopsType = dt.Rows[i]["ShopsType"].TString();
                       shop.ShopsPraise = dt.Rows[i]["ShopsPraise"].TInt32();
                       shop.ShopsPoor = dt.Rows[i]["ShopsPoor"].TInt32();
                       shop.OpeningTime = dt.Rows[0]["OpeningTime"].TTime();
                       shop.ClosingTime = dt.Rows[0]["ClosingTime"].TTime();
                       shop.DeliveryDistance = dt.Rows[i]["DeliveryDistance"].TDecimal();
                       shop.LowestPrice = dt.Rows[i]["LowestPrice"].TDecimal();
                       shop.Freight = dt.Rows[0]["Freight"].TDecimal();
                       shop.Consumption = dt.Rows[i]["Consumption"].TDecimal();
                       shop.RecommendedDishes = dt.Rows[i]["RecommendedDishes"].TString();
                       shop.Latitude = dt.Rows[i]["Latitude"].TDouble();
                       shop.Longitude = dt.Rows[i]["Longitude"].TDouble();
                       shop.Rec_CreateTime = dt.Rows[i]["Rec_CreateTime"].TDateTime();
                       list.Add(shop);
                   }
               }
               return list;
           }
           catch (Exception e)
           {
               base.WriteLog(e.Message);
               return null;
           }
       }
    }
}
